package com.education.welco.service.impl;


import com.education.common.config.BusinessException;
import com.education.common.pojo.TStudent;
import com.education.common.utils.Convert;
import com.education.welco.controller.TStudentController;
import com.education.welco.mapper.TStudentMapper;
import com.education.welco.pojo.TClass;
import com.education.welco.pojo.TSchooldept;
import com.education.welco.service.ITClassService;
import com.education.welco.service.ITSchooldeptService;
import com.education.welco.service.ITStudentService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 学生入学 服务层实现
 *
 * @author d
 * @date 2020-05-08
 */
@Service
public class TStudentServiceImpl implements ITStudentService {

    @Autowired
    private TStudentMapper tStudentMapper;
    @Autowired
    private ITSchooldeptService schooldeptService;
    @Autowired
    private ITClassService classService;
    @Autowired
    private TStudentController tStudentController;

    /**
     * @param phone 唯一标识 手机号
     * @return
     */
    @Override
    public TStudent judgeStudent(String phone) {
        return tStudentMapper.judgeStudent(phone);
    }

    @Override
    public TStudent judgeStudent2(String phone) {
        return tStudentMapper.judgeStudent2(phone);
    }

    @Override
    public Integer selectStudentNumByClassId(List<Integer> classIdList) {
        return tStudentMapper.selectStudentNumByClassId(classIdList);
    }

    @Override
    public List<Integer> selectClassIdListByTeaId(Integer teacherId) {
        return tStudentMapper.selectClassIdListByTeaId(teacherId);
    }

    @Override
    public List<TClass> selectClassListByTeaId(Integer teacherUserId) {
        return tStudentMapper.selectClassListByTeaId(teacherUserId);
    }

    /**
     * 根据学生用户id查询学生信息和班级信息
     *
     * @param userId
     * @return
     */
    @Override
    public TStudent queryStudentAndClassByStuUserId(Integer userId) {
        return tStudentMapper.queryStudentAndClassByStuUserId(userId);
    }

    //app端登录，并创建登录时间
    @Override
    public TStudent selectClassInfoByPhone(String phone) {
        return tStudentMapper.selectClassInfoByPhone(phone);
    }

    @Override
    public int insertUserRoleInUser(Integer userId, Integer roleId) {
        return tStudentMapper.insertUserRoleInUser(userId, roleId);
    }

    /**
     * 查询学生入学信息
     *
     * @param id 学生入学ID
     * @return 学生入学信息
     */
    @Override
    public TStudent selectTStudentById(Integer id) {
        return tStudentMapper.selectTStudentById(id);
    }

    @Override
    public TStudent selectTStudentById2(Integer userId) {
        return tStudentMapper.selectTStudentById2(userId);
    }

    /**
     * 查询学生入学列表
     *
     * @param tStudent 学生入学信息
     * @return 学生入学集合
     */
    @Override
    public List<TStudent> selectTStudentListOther2(TStudent tStudent) {
        return tStudentMapper.selectTStudentListOther2(tStudent);
    }

    @Override
    public List<TStudent> selectStudentListRemovebohui(TStudent tStudent) {
        return tStudentMapper.selectStudentListRemovebohui(tStudent);
    }

    /**
     * 查询学生入学列表
     *
     * @param tStudent 学生入学信息
     * @return 学生入学集合
     */
    @Override
    public List<TStudent> selectTStudentListOther(TStudent tStudent) {
        return tStudentMapper.selectTStudentListOther(tStudent);
    }

    /**
     * 查询学生入学列表
     *
     * @param tStudent 学生入学信息
     * @return 学生入学集合
     */
    @Override
    public List<TStudent> selectTStudentList(TStudent tStudent) {
        return tStudentMapper.selectTStudentList(tStudent);
    }

    @Override
    public List<TStudent> selectTStudentList1(Integer classId) {
        return tStudentMapper.selectTStudentList1(classId);
    }

    /**
     * 查询大于等于此id 的数据列表
     *
     * @param id
     * @return
     */
    @Override
    public List<TStudent> selectTStudentByIdGreater(Integer id) {
        return tStudentMapper.selectTStudentByIdGreater(id);
    }

    /**
     * 新增学生入学
     *
     * @param tStudent 学生入学信息
     * @return 结果
     */
    @Override
    public int insertTStudent(TStudent tStudent) {
        return tStudentMapper.insertTStudent(tStudent);
    }

    @Override
    public int updateInfoForUser(Integer userId, Integer classId, Integer schoolId, Integer sysType, Integer customerType) {
        return tStudentMapper.updateInfoForUser(userId, classId, schoolId, sysType, customerType);
    }

    /**
     * 修改学生入学
     *
     * @param tStudent 学生入学信息
     * @return 结果
     */
    @Override
    public int updateTStudent(TStudent tStudent) {
        return tStudentMapper.updateTStudent(tStudent);
    }

    @Override
    public int updateByUserId(TStudent tStudent) {
        return tStudentMapper.updateByUserId(tStudent);
    }

    @Override
    public int deleteTStudentById(Integer id) {
        return tStudentMapper.deleteTStudentById(id);
    }

    /**
     * 删除学生入学对象
     *
     * @param ids 需要删除的数据ID
     * @return 结果
     */
    @Override
    public int deleteTStudentByIds(String ids) {
        return tStudentMapper.deleteTStudentByIds(Convert.toStrArray(ids));
    }


    /**
     * 导入学生入学对象信息
     *
     * @param fileName 需要删除的数据ID
     * @return 结果
     */
    @Transactional(rollbackFor = RuntimeException.class)
    @Override
    public boolean threeBatchQuesionImport(String fileName, MultipartFile file, Integer schoolId) throws Exception {
        //批量导入的学校列表
        List<TStudent> studentArrayList = new ArrayList<>();
        //从上传的Excel表格里面取出对象添加到数据库中导入
        //定义一个标记，判断该文件是否为空
        boolean notNull = false;
        //matches  正则表达式    判断是不是表格的形式
        //xls 复合文档类型的结构     xlsx    xml类型结构
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            //throw new Exception("上传文件格式不正确");
            throw new BusinessException(201, "上传文件格式不正确");
        }
        boolean isExcel1903 = true;
        //判断是不是表格的形式
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel1903 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb;
        if (isExcel1903) {
            //HSSFWorkbook操作Excel1903以前（包括1903）的版本 扩展名是.xls
            wb = new HSSFWorkbook(is);
        } else {
            //XSSFWorkbook操作Excel1907的版本，扩展名是.xlsx
            wb = new XSSFWorkbook(is);
        }
        //getSheetAt(0) 获取到excel的第一个sheet页
        Sheet sheet = wb.getSheetAt(0);
        if (sheet != null) {
            notNull = true;
        }
        TStudent student;
        //获取总行数
        //r = 2 表示从第三行开始循环 如果你的第三行开始是数据
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            //通过sheet表单对象得到 行对象
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;//结束本次循环，开始下次循环
            }
            if (Objects.isNull(row.getCell(0))) {
                continue;
            }
            String stringCellValue = row.getCell(0).getStringCellValue();
            if ("".equals(stringCellValue + "")) {
                continue;
            }

            //完整的循环一次
            student = new TStudent();
            student.setSchoolId(schoolId);
            //取出数据并赋值
            //学生姓名
            if (row.getCell(0) == null) {
                row.createCell(0);
            } else {
                //获取单元格
                Cell cell = row.getCell(0);
                //设置单元格类型
                cell.setCellType(CellType.STRING);
                //获取单元格数据
                String studentName = cell.getStringCellValue();
                student.setStudentName(studentName);
            }
            //学号
            String studentCode = "";
            if (row.getCell(1) == null) {
                row.createCell(1);
            } else {
                Cell cell = row.getCell(1);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    studentCode = cell.getStringCellValue();
                }
                student.setStudentCode(studentCode);
            }
            //所属院系
            if (row.getCell(2) == null) {
                row.createCell(2);
            } else {
                //获取单元格
                Cell cell = row.getCell(2);
                //设置单元格类型
                cell.setCellType(CellType.STRING);
                //获取单元格数据
                String deptName = cell.getStringCellValue();

                TSchooldept schooldept = new TSchooldept();
                schooldept.setDeptName(deptName);
                schooldept.setSchoolId(schoolId);
                List<TSchooldept> schooldeptList = schooldeptService.selectTSchooldeptList(schooldept);
                //部门名称不规范
                if (schooldeptList.size() != 1) {
                    throw new RuntimeException("导入第" + (r + 1) + "行失败,请检查所属院系");
                    //throw  new BusinessException(201,"导入第" + (r + 1) + "行失败,请检查所属院系");
                }
            }
            //所在班级
            if (row.getCell(3) == null) {
                row.createCell(3);
            } else {
                //获取单元格
                Cell cell = row.getCell(3);
                //设置单元格类型
                cell.setCellType(CellType.STRING);
                //获取单元格数据
                String className = cell.getStringCellValue();
                TClass tClass = new TClass();
                tClass.setClassName(className);
                tClass.setSchoolId(schoolId);
                List<TClass> classList = classService.selectTClassList(tClass);
                //班级名称不规范
                if (classList.size() != 1) {
                    throw new RuntimeException("导入第" + (r + 1) + "行失败,请检查所在班级");
                    //throw  new BusinessException(201,"导入第" + (r + 1) + "行失败,请检查所在班级");
                }
                student.setClassId(classList.get(0).getId());
            }
            //证件号码
            if (row.getCell(4) == null) {
                row.createCell(4);
            } else {
                //获取单元格
                Cell cell = row.getCell(4);
                //设置单元格类型
                cell.setCellType(CellType.STRING);
                //获取单元格数据
                String idcardno = cell.getStringCellValue();
                // 定义判别用户身份证号的正则表达式（15位或者18位，最后一位可以为字母）
                //String regularExpression = "(^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|" +
                //        "(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
                //假设18位身份证号码:41000119910101123X  410001 19910101 123X
                //^开头
                //[1-9] 第一位1-9中的一个      4
                //\\d{5} 五位数字           10001（前六位省市县地区）
                //(18|19|20)                19（现阶段可能取值范围18xx-20xx年）
                //\\d{2}                    91（年份）
                //((0[1-9])|(10|11|12))     01（月份）
                //(([0-2][1-9])|10|20|30|31)01（日期）
                //\\d{3} 三位数字            123（第十七位奇数代表男，偶数代表女）
                //[0-9Xx] 0123456789Xx其中的一个 X（第十八位为校验值）
                //$结尾

                //假设15位身份证号码:410001910101123  410001 910101 123
                //^开头
                //[1-9] 第一位1-9中的一个      4
                //\\d{5} 五位数字           10001（前六位省市县地区）
                //\\d{2}                    91（年份）
                //((0[1-9])|(10|11|12))     01（月份）
                //(([0-2][1-9])|10|20|30|31)01（日期）
                //\\d{3} 三位数字            123（第十五位奇数代表男，偶数代表女），15位身份证不含X
                //$结尾

                //boolean matches = idcardno.matches(regularExpression);
                //判断第18位校验值
                //if (matches) {
                //    if (idcardno.length() == 18) {
                //        try {
                //            char[] charArray = idcardno.toCharArray();
                //前十七位加权因子
                //            int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
                //这是除以11后，可能产生的11位余数对应的验证码
                //            String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
                //            int sum = 0;
                //            for (int i = 0; i < idCardWi.length; i++) {
                //                int current = Integer.parseInt(String.valueOf(charArray[i]));
                //                int count = current * idCardWi[i];
                //                sum += count;
                //            }
                //            char idCardLast = charArray[17];
                //            int idCardMod = sum % 11;
                //            if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
                //                student.setIdcardno(idcardno);
                //            } else {
                //                throw new RuntimeException("导入第" + (r + 1) + "行失败,请检查身份证号码");
                //                throw  new BusinessException(201,"导入第" + (r + 1) + "行失败,请检查身份证号码");
                //            }
                //        } catch (Exception e) {
                //            e.printStackTrace();
                //            return false;
                //        }
                //    }
                //} else {
                //    throw new RuntimeException("导入第" + (r + 1) + "行失败,请检查身份证号码");
                //    throw  new BusinessException(201,"导入第" + (r + 1) + "行失败,请检查身份证号码");
                //}
                if (!"".equals(idcardno)) {
                    int i = 18;
                    if (i != idcardno.length()) {
                        throw new RuntimeException("导入第 " + (r + 1) + "行失败,身份证号格式不正确");
                    }
                    TStudent tStudent = new TStudent();
                    tStudent.setIdcardno(idcardno);
                    List<TStudent> tStudents = this.selectTStudentList(tStudent);
                    if (tStudents.size() > 0) {
                        throw new RuntimeException("导入第 " + (r + 1) + "行失败,身份证号已存在 ");
                    } else {
                        student.setIdcardno(idcardno);
                    }
                }
            }
            //账号
            String phone = "";
            if (row.getCell(5) == null) {
                row.createCell(5);
            } else {
                Cell cell = row.getCell(5);
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    phone = cell.getStringCellValue();
                }
                if (!"".equals(phone)) {
                    TStudent tStudent = new TStudent();
                    tStudent.setPhone(phone);
                    List<TStudent> tStudents = this.selectTStudentList(tStudent);
                    if (tStudents.size() > 0) {
                        throw new RuntimeException("导入第 " + (r + 1) + "行失败,手机号已存在 ");
                    }
                }
                student.setPhone(phone);
            }
            studentArrayList.add(student);
        }
        //调用com.education.welco.controller.TStudentController.addByTea方法
        for (TStudent tStudent : studentArrayList) {
            tStudentController.addByTea(tStudent);
        }
        return notNull;
    }

}